# 批量操作：批量操作Excel测试用例的结果

import openpyxl

path = r'C:\Users\suyujie\Desktop\用户接口测试用例.xlsx'
workbook = openpyxl.load_workbook(path)
worksheet = workbook['Sheet2']

for i in range(2,worksheet.max_row+1):
    expected_result = worksheet.cell(i,11)
    print(expected_result.value)
    fact_result = worksheet.cell(i,15)
    print(fact_result.value)
    if fact_result.value == None:
        continue
    result = worksheet.cell(i, 14)
    if str(expected_result.value) in fact_result.value:
        result.value='通过'
    elif str(expected_result.value)=='无' and str(result.value)not in(100,200,300,400,500):
        result.value = '通过'
    else:
        result.value = '不通过'
    print(result.value)#11列预期结果 15列实际返回结果 ，对比后将结果放在第14列
workbook.save(path)
print(worksheet.max_row)

"""
    计算通过率
"""
path = r'C:\Users\suyujie\Desktop\用户接口测试用例.xlsx'
workbook = openpyxl.load_workbook(path)
worksheet = workbook['Sheet2']
total_count=worksheet.max_row-1
pass_count=0
for i in range(2,worksheet.max_row+1):
    if worksheet.cell(i,14).value=='通过':
        pass_count+=1
pass_rate = float(pass_count/total_count)*100
print(f'总用力数量:{total_count},通过条数:{pass_count},未通过条数:{total_count-pass_count},通过率:{pass_rate:.2f}%')